﻿/*
Deployment script for EventHandlerDatabase_Release
*/


GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;



GO


GO
USE [master]


GO
IF (DB_ID(N'EventHandlerDatabase_Release') IS NOT NULL
    AND DATABASEPROPERTYEX(N'EventHandlerDatabase_Release','Status') <> N'ONLINE')
BEGIN
    RAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'EventHandlerDatabase_Release') WITH NOWAIT
    RETURN
END


GO
IF (DB_ID(N'EventHandlerDatabase_Release') IS NOT NULL) 
BEGIN
    ALTER DATABASE [EventHandlerDatabase_Release]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [EventHandlerDatabase_Release];
END


GO
PRINT N'Creating EventHandlerDatabase_Release...'

GO
declare @rc int, @dir nvarchar(4000) 
exec @rc = master.dbo.xp_instance_regread
           N'HKEY_LOCAL_MACHINE',      
           N'Software\Microsoft\MSSQLServer\Setup',      
           N'SQLPath',       
           @dir output, 
           'no_output'

DECLARE @sql AS NVARCHAR(MAX);
DECLARE @s AS NVARCHAR(1);

SET @s = '''';

SET @sql = '
CREATE DATABASE [EventHandlerDatabase_Release]
    ON 
    PRIMARY(NAME = [EventHandler], FILENAME = ' + @s + @dir + '\DATA\EventHandlerDatabase_Release.mdf' + @s + ', MAXSIZE = UNLIMITED, FILEGROWTH = 10240 KB)
    LOG ON (NAME = [EventHandler_log], FILENAME = ' + @s + @dir + '\DATA\EventHandlerDatabase_Release_log.ldf' + @s + ', MAXSIZE = 2097152 MB, FILEGROWTH = 10240 KB)';
    
PRINT @sql;

EXEC sp_executesql @sql;
GO
EXECUTE sp_dbcmptlevel [EventHandlerDatabase_Release], 100;



GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'EventHandlerDatabase_Release')
    BEGIN
        ALTER DATABASE [EventHandlerDatabase_Release]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                NUMERIC_ROUNDABORT OFF,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL,
                RECOVERY FULL,
                CURSOR_CLOSE_ON_COMMIT OFF,
                AUTO_CREATE_STATISTICS ON,
                AUTO_SHRINK OFF,
                AUTO_UPDATE_STATISTICS ON,
                RECURSIVE_TRIGGERS OFF 
            WITH ROLLBACK IMMEDIATE;
        ALTER DATABASE [EventHandlerDatabase_Release]
            SET AUTO_CLOSE OFF 
            WITH ROLLBACK IMMEDIATE;
    END



GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'EventHandlerDatabase_Release')
    BEGIN
        ALTER DATABASE [EventHandlerDatabase_Release]
            SET ALLOW_SNAPSHOT_ISOLATION OFF;
    END



GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'EventHandlerDatabase_Release')
    BEGIN
        ALTER DATABASE [EventHandlerDatabase_Release]
            SET READ_COMMITTED_SNAPSHOT OFF;
    END



GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'EventHandlerDatabase_Release')
    BEGIN
        ALTER DATABASE [EventHandlerDatabase_Release]
            SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
                PAGE_VERIFY NONE,
                DATE_CORRELATION_OPTIMIZATION OFF,
                DISABLE_BROKER,
                PARAMETERIZATION SIMPLE 
            WITH ROLLBACK IMMEDIATE;
    END



GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'EventHandlerDatabase_Release')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [EventHandlerDatabase_Release]
    SET TRUSTWORTHY OFF,
        DB_CHAINING OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings for DB_CHAINING or TRUSTWORTHY cannot be modified. You must be a SysAdmin to apply these settings.';
    END



GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'EventHandlerDatabase_Release')
    BEGIN
        ALTER DATABASE [EventHandlerDatabase_Release]
            SET HONOR_BROKER_PRIORITY OFF 
            WITH ROLLBACK IMMEDIATE;
    END



GO
USE [EventHandlerDatabase_Release]


GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';



GO


GO
/*
 Pre-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be executed before the build script.	
 Use SQLCMD syntax to include a file in the pre-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the pre-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/


GO


GO
PRINT N'Creating EventHandlerWeb...';



GO
CREATE USER [EventHandlerWeb] FOR LOGIN [EventHandlerWeb];



GO
PRINT N'Creating Auth...';



GO
CREATE SCHEMA [Auth]
    AUTHORIZATION [dbo];



GO
PRINT N'Creating Event...';



GO
CREATE SCHEMA [Event]
    AUTHORIZATION [dbo];



GO
PRINT N'Creating Auth.AccountProperties...';



GO
CREATE TABLE [Auth].[AccountProperties] (
    [AccountPropertyID] BIGINT        IDENTITY (1, 1) NOT NULL,
    [AccountID]         INT           NOT NULL,
    [PropertyName]      VARCHAR (50)  NOT NULL,
    [PropertyValue]     VARCHAR (MAX) NOT NULL
);



GO
PRINT N'Creating Auth.Accounts...';



GO
CREATE TABLE [Auth].[Accounts] (
    [AccountID]                INT            IDENTITY (1001, 1) NOT NULL,
    [FirstName]                NVARCHAR (30)  NOT NULL,
    [LastName]                 NVARCHAR (30)  NOT NULL,
    [DisplayName]              NVARCHAR (255) NOT NULL,
    [Password]                 NVARCHAR (255) NOT NULL,
    [EmailAddress]             NVARCHAR (255) NOT NULL,
    [AuthenticationSignInName] NVARCHAR (255) NOT NULL,
    [AuthenticationTypeID]     SMALLINT       NOT NULL
);



GO
PRINT N'Creating Auth.AuthenticationType...';



GO
CREATE TABLE [Auth].[AuthenticationType] (
    [AuthenticationTypeID]   SMALLINT     IDENTITY (1, 1) NOT NULL,
    [AuthenticationTypeName] VARCHAR (50) NOT NULL
);



GO
PRINT N'Creating Event.AttendeeListVisibility...';



GO
CREATE TABLE [Event].[AttendeeListVisibility] (
    [AttendeeListVisibilityID]          SMALLINT       IDENTITY (1, 1) NOT NULL,
    [AttendeeListVisibilityDescription] NVARCHAR (255) NOT NULL
);



GO
PRINT N'Creating Event.Attendees...';



GO
CREATE TABLE [Event].[Attendees] (
    [AttendeeID]   INT            IDENTITY (1, 1) NOT NULL,
    [EventID]      INT            NOT NULL,
    [AccountID]    INT            NULL,
    [EmailAddress] NVARCHAR (255) NULL,
    [Response]     BIT            NULL,
    [Attended]     BIT            NULL
);



GO
PRINT N'Creating Event.Events...';



GO
CREATE TABLE [Event].[Events] (
    [EventID]                  INT             IDENTITY (1, 1) NOT NULL,
    [OwnerID]                  INT             NOT NULL,
    [LocationID]               INT             NULL,
    [AttendeeListVisibilityID] SMALLINT        NOT NULL,
    [Title]                    NVARCHAR (255)  NOT NULL,
    [Description]              NVARCHAR (1000) NULL,
    [Cost]                     MONEY           NOT NULL,
    [PaymentAccepted]          BIT             NOT NULL,
    [PaymentRefundable]        BIT             NOT NULL,
    [InvitationOnly]           BIT             NOT NULL,
    [Searchable]               BIT             NOT NULL,
    [SearchEngineURL]          NVARCHAR (200)  NULL
);



GO
PRINT N'Creating Event.Invitations...';



GO
CREATE TABLE [Event].[Invitations] (
    [InvitationID]   INT            IDENTITY (1, 1) NOT NULL,
    [EventID]        INT            NOT NULL,
    [BodyText]       NVARCHAR (MAX) NULL,
    [InvitationSent] SMALLDATETIME  NULL
);



GO
PRINT N'Creating Event.Locations...';



GO
CREATE TABLE [Event].[Locations] (
    [LocationID]   INT               IDENTITY (1, 1) NOT NULL,
    [Title]        NVARCHAR (255)    NOT NULL,
    [Address1]     NVARCHAR (255)    NULL,
    [Address2]     NVARCHAR (255)    NULL,
    [City]         NVARCHAR (50)     NULL,
    [State]        NVARCHAR (50)     NULL,
    [PostalCode]   NVARCHAR (10)     NULL,
    [Directions]   NVARCHAR (2000)   NULL,
    [Coordinates]  [sys].[geography] NULL,
    [Coordinates2] AS                Coordinates.STAsText()
);



GO
PRINT N'Creating Event.Schedule...';



GO
CREATE TABLE [Event].[Schedule] (
    [ScheduleID]  INT            IDENTITY (1, 1) NOT NULL,
    [EventID]     INT            NOT NULL,
    [Description] NVARCHAR (255) NULL,
    [StartTime]   SMALLDATETIME  NOT NULL,
    [EndTime]     SMALLDATETIME  NULL
);



GO
PRINT N'Creating Event.AttendeeList...';



GO
CREATE TYPE [Event].[AttendeeList] AS  TABLE (
    [AccountID]    INT            NULL,
    [EmailAddress] NVARCHAR (255) NULL);



GO
PRINT N'Creating On column: Cost...';



GO
ALTER TABLE [Event].[Events]
    ADD DEFAULT 0 FOR [Cost];



GO
PRINT N'Creating On column: PaymentAccepted...';



GO
ALTER TABLE [Event].[Events]
    ADD DEFAULT 0 FOR [PaymentAccepted];



GO
PRINT N'Creating On column: PaymentRefundable...';



GO
ALTER TABLE [Event].[Events]
    ADD DEFAULT 0 FOR [PaymentRefundable];



GO
PRINT N'Creating On column: InvitationOnly...';



GO
ALTER TABLE [Event].[Events]
    ADD DEFAULT 0 FOR [InvitationOnly];



GO
PRINT N'Creating On column: Searchable...';



GO
ALTER TABLE [Event].[Events]
    ADD DEFAULT 1 FOR [Searchable];



GO
PRINT N'Creating Auth.UX_Accounts_AccountSignInName...';



GO
ALTER TABLE [Auth].[Accounts]
    ADD CONSTRAINT [UX_Accounts_AccountSignInName] UNIQUE NONCLUSTERED ([AuthenticationSignInName] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);



GO
PRINT N'Creating Auth.PK_AccountProperties...';



GO
ALTER TABLE [Auth].[AccountProperties]
    ADD CONSTRAINT [PK_AccountProperties] PRIMARY KEY CLUSTERED ([AccountPropertyID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);



GO
PRINT N'Creating Auth.PK_Accounts...';



GO
ALTER TABLE [Auth].[Accounts]
    ADD CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED ([AccountID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);



GO
PRINT N'Creating Auth.PK_AuthenticationType...';



GO
ALTER TABLE [Auth].[AuthenticationType]
    ADD CONSTRAINT [PK_AuthenticationType] PRIMARY KEY CLUSTERED ([AuthenticationTypeID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);



GO
PRINT N'Creating Event.PK_AttendeeListVisibility...';



GO
ALTER TABLE [Event].[AttendeeListVisibility]
    ADD CONSTRAINT [PK_AttendeeListVisibility] PRIMARY KEY CLUSTERED ([AttendeeListVisibilityID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);



GO
PRINT N'Creating Event.PK_Attendees...';



GO
ALTER TABLE [Event].[Attendees]
    ADD CONSTRAINT [PK_Attendees] PRIMARY KEY CLUSTERED ([AttendeeID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);



GO
PRINT N'Creating Event.PK_Events...';



GO
ALTER TABLE [Event].[Events]
    ADD CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED ([EventID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);



GO
PRINT N'Creating Event.PK_Location...';



GO
ALTER TABLE [Event].[Locations]
    ADD CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED ([LocationID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);



GO
PRINT N'Creating Event.PK_Schedule...';



GO
ALTER TABLE [Event].[Schedule]
    ADD CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED ([ScheduleID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);



GO
PRINT N'Creating Event.Locations.IX_Location_Coordinates_Spatial...';



GO
CREATE SPATIAL INDEX [IX_Location_Coordinates_Spatial]
    ON [Event].[Locations] ([Coordinates])
    WITH  (
            CELLS_PER_OBJECT = 16,
            GRIDS = (LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM),
            ONLINE = OFF
          );



GO
PRINT N'Creating Auth.FK_AccountProperties_Accounts...';



GO
ALTER TABLE [Auth].[AccountProperties]
    ADD CONSTRAINT [FK_AccountProperties_Accounts] FOREIGN KEY ([AccountID]) REFERENCES [Auth].[Accounts] ([AccountID]) ON DELETE NO ACTION ON UPDATE NO ACTION;



GO
PRINT N'Creating Auth.FK_Accounts_AuthenticationType...';



GO
ALTER TABLE [Auth].[Accounts]
    ADD CONSTRAINT [FK_Accounts_AuthenticationType] FOREIGN KEY ([AuthenticationTypeID]) REFERENCES [Auth].[AuthenticationType] ([AuthenticationTypeID]) ON DELETE NO ACTION ON UPDATE NO ACTION;



GO
PRINT N'Creating Event.FK_Attendees_Accounts...';



GO
ALTER TABLE [Event].[Attendees]
    ADD CONSTRAINT [FK_Attendees_Accounts] FOREIGN KEY ([AccountID]) REFERENCES [Auth].[Accounts] ([AccountID]) ON DELETE NO ACTION ON UPDATE NO ACTION;



GO
PRINT N'Creating Event.FK_Attendees_Events...';



GO
ALTER TABLE [Event].[Attendees]
    ADD CONSTRAINT [FK_Attendees_Events] FOREIGN KEY ([EventID]) REFERENCES [Event].[Events] ([EventID]) ON DELETE NO ACTION ON UPDATE NO ACTION;



GO
PRINT N'Creating Event.FK_Events_Accounts...';



GO
ALTER TABLE [Event].[Events]
    ADD CONSTRAINT [FK_Events_Accounts] FOREIGN KEY ([OwnerID]) REFERENCES [Auth].[Accounts] ([AccountID]) ON DELETE NO ACTION ON UPDATE NO ACTION;



GO
PRINT N'Creating Event.FK_Events_AttendeeListVisibility...';



GO
ALTER TABLE [Event].[Events]
    ADD CONSTRAINT [FK_Events_AttendeeListVisibility] FOREIGN KEY ([AttendeeListVisibilityID]) REFERENCES [Event].[AttendeeListVisibility] ([AttendeeListVisibilityID]) ON DELETE NO ACTION ON UPDATE NO ACTION;



GO
PRINT N'Creating Event.FK_Events_Locations...';



GO
ALTER TABLE [Event].[Events]
    ADD CONSTRAINT [FK_Events_Locations] FOREIGN KEY ([LocationID]) REFERENCES [Event].[Locations] ([LocationID]) ON DELETE NO ACTION ON UPDATE NO ACTION;



GO
PRINT N'Creating Event.FK_Schedule_Event...';



GO
ALTER TABLE [Event].[Schedule]
    ADD CONSTRAINT [FK_Schedule_Event] FOREIGN KEY ([EventID]) REFERENCES [Event].[Events] ([EventID]) ON DELETE NO ACTION ON UPDATE NO ACTION;



GO
PRINT N'Creating Auth.CK_Accounts_PasswordScheme...';



GO
ALTER TABLE [Auth].[Accounts]
    ADD CONSTRAINT [CK_Accounts_PasswordScheme] CHECK ((AuthenticationTypeID = 1 AND COALESCE(LEN(Password), 0) > 0)
	        OR
	        (AuthenticationTypeID <> 1 AND Password IS NULL));



GO
PRINT N'Creating Auth.CreateNewAccount...';



GO
CREATE PROCEDURE [Auth].[CreateNewAccount]
@FirstName NVARCHAR (30), @LastName NVARCHAR (30), @DisplayName NVARCHAR (255)=NULL, @Password NVARCHAR (255)=NULL, @EmailAddress NVARCHAR (255), @AuthenticationSignInName NVARCHAR (255)=NULL, @AuthenticationTypeID SMALLINT, @AccountID INT OUTPUT
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;
BEGIN TRY
  BEGIN TRANSACTION
  
  IF @DisplayName IS NULL
    SET @DisplayName = @FirstName + ' ' + @LastName;
  
  IF @AuthenticationSignInName IS NULL
    SET @AuthenticationSignInName = @EmailAddress;
  
  INSERT INTO Auth.Accounts
  (
    FirstName,
    LastName,
    DisplayName,
    Password,
    EmailAddress,
    AuthenticationSignInName,
    AuthenticationTypeId
  )
  VALUES
  (
    @FirstName,
    @LastName,
    @DisplayName,
    @Password,
    @EmailAddress,
    @AuthenticationSignInName,
    @AuthenticationTypeID
  )
  
  SET @AccountID = SCOPE_IDENTITY();
  
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  SET @AccountID = -1;
  
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  
  DECLARE @ERROR_SEVERITY AS INT,
    @ERROR_STATE AS INT,
    @ERROR_NUMBER AS INT,
    @ERROR_LINE AS INT,
    @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT
    @ERROR_SEVERITY = ERROR_SEVERITY(),
    @ERROR_STATE = ERROR_STATE(),
    @ERROR_NUMBER = ERROR_NUMBER(),
    @ERROR_LINE = ERROR_LINE(),
    @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
    @ERROR_SEVERITY,
    @ERROR_STATE,
    @ERROR_NUMBER,
    @ERROR_LINE,
    @ERROR_MESSAGE);

END CATCH



GO
PRINT N'Creating Auth.DeleteAccount...';



GO
CREATE PROCEDURE [Auth].[DeleteAccount]
@AccountID INT
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

BEGIN TRY
  BEGIN TRANSACTION
    DELETE Event.Attendees
     WHERE AccountID = @AccountID;
     
    DELETE ei
      FROM Event.[Invitations] AS ei
           INNER JOIN Event.Events AS ee
              ON ei.EventID = ee.EventID
     WHERE ee.OwnerID = @AccountID;
    
    DELETE es
      FROM Event.Schedule AS es
           INNER JOIN Event.Events AS ee
              ON es.EventID = ee.EventID
     WHERE ee.OwnerID = @AccountID;
    
    DELETE ea
      FROM Event.Attendees AS ea
           INNER JOIN Event.Events AS ee
              ON ea.EventID = ee.EventID
     WHERE ee.OwnerID = @AccountID;
    
    DELETE Event.Events
     WHERE OwnerID = @AccountID;
    
    DELETE Auth.AccountProperties
     WHERE AccountID = @AccountID;
    
    DELETE Auth.Accounts
     WHERE AccountID = @AccountID;
    
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  
  DECLARE @ERROR_SEVERITY AS INT,
          @ERROR_STATE AS INT,
          @ERROR_NUMBER AS INT,
          @ERROR_LINE AS INT,
          @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT @ERROR_SEVERITY = ERROR_SEVERITY(),
         @ERROR_STATE = ERROR_STATE(),
         @ERROR_NUMBER = ERROR_NUMBER(),
         @ERROR_LINE = ERROR_LINE(),
         @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR_SEVERITY,
            @ERROR_STATE,
            @ERROR_NUMBER,
            @ERROR_LINE,
            @ERROR_MESSAGE);
END CATCH



GO
PRINT N'Creating Auth.GetUserByAccountID...';



GO
CREATE PROCEDURE [Auth].[GetUserByAccountID]
@AccountID INT
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

SELECT AccountID,
       FirstName,
       LastName,
       DisplayName,
       Password,
       EmailAddress,
       AuthenticationSignInName,
       AuthenticationTypeID
  FROM Auth.Accounts
 WHERE AccountID = @AccountID;

SELECT AccountPropertyID,
       AccountID,
       PropertyName,
       PropertyValue
  FROM Auth.AccountProperties
 WHERE AccountID = @AccountID;



GO
PRINT N'Creating Auth.GetUserByUserName...';



GO
CREATE PROCEDURE [Auth].[GetUserByUserName]
@UserName NVARCHAR (255)
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

SELECT a.AccountID,
       a.FirstName,
       a.LastName,
       a.DisplayName,
       a.Password,
       a.AuthenticationSignInName,
       a.AuthenticationTypeID,
       a_type.AuthenticationTypeName
  FROM Auth.Accounts AS a
       INNER JOIN Auth.AuthenticationType AS a_type
          ON a.AuthenticationTypeID = a_type.AuthenticationTypeID
 WHERE a.AuthenticationSignInName = @UserName;



GO
PRINT N'Creating Auth.UpdateAccount...';



GO
CREATE PROCEDURE [Auth].[UpdateAccount]
@AccountID INT, @FirstName NVARCHAR (30), @LastName NVARCHAR (30), @Password NVARCHAR (255)=NULL, @DisplayName NVARCHAR (255), @AuthenticationSignInName NVARCHAR (255), @AuthenticationTypeID SMALLINT
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

BEGIN TRY
  BEGIN TRANSACTION
  
  UPDATE Auth.Accounts
     SET FirstName = @FirstName,
         LastName = @LastName,
         DisplayName = @DisplayName,
         Password = @Password,
         AuthenticationSignInName = @AuthenticationSignInName,
         AuthenticationTypeID = @AuthenticationTypeID
   WHERE AccountID = @AccountID;
  
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  
  DECLARE @ERROR_SEVERITY AS INT,
          @ERROR_STATE AS INT,
          @ERROR_NUMBER AS INT,
          @ERROR_LINE AS INT,
          @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT @ERROR_SEVERITY = ERROR_SEVERITY(),
         @ERROR_STATE = ERROR_STATE(),
         @ERROR_NUMBER = ERROR_NUMBER(),
         @ERROR_LINE = ERROR_LINE(),
         @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR_SEVERITY,
            @ERROR_STATE,
            @ERROR_NUMBER,
            @ERROR_LINE,
            @ERROR_MESSAGE);

END CATCH



GO
PRINT N'Creating Event.AddSchedule...';



GO
CREATE PROCEDURE [Event].[AddSchedule]
@EventID INT, @Description NVARCHAR (255)=NULL, @StartTime SMALLDATETIME, @EndTime SMALLDATETIME=NULL, @ScheduleID INT OUTPUT
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;
BEGIN TRY
  BEGIN TRANSACTION
  
  INSERT INTO Event.Schedule
  (
    EventID,
    Description,
    StartTime,
    EndTime
  )
  VALUES
  (
    @EventID,
    @Description,
    @StartTime,
    @EndTime
  );
  
  SET @ScheduleID = SCOPE_IDENTITY();
  
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  SET @ScheduleID = -1;
  
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  
  DECLARE @ERROR_SEVERITY AS INT,
          @ERROR_STATE AS INT,
          @ERROR_NUMBER AS INT,
          @ERROR_LINE AS INT,
          @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT @ERROR_SEVERITY = ERROR_SEVERITY(),
         @ERROR_STATE = ERROR_STATE(),
         @ERROR_NUMBER = ERROR_NUMBER(),
         @ERROR_LINE = ERROR_LINE(),
         @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR_SEVERITY,
            @ERROR_STATE,
            @ERROR_NUMBER,
            @ERROR_LINE,
            @ERROR_MESSAGE);
END CATCH



GO
PRINT N'Creating Event.CancelAttendance...';



GO
CREATE PROCEDURE [Event].[CancelAttendance]
@EventID INT, @AccountID INT
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

BEGIN TRY
  BEGIN TRANSACTION
  
  DELETE FROM Event.Attendees
   WHERE EventID = @EventID
     AND AccountID = @AccountID;
  
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  
  DECLARE @ERROR_SEVERITY AS INT,
          @ERROR_STATE AS INT,
          @ERROR_NUMBER AS INT,
          @ERROR_LINE AS INT,
          @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT @ERROR_SEVERITY = ERROR_SEVERITY(),
         @ERROR_STATE = ERROR_STATE(),
         @ERROR_NUMBER = ERROR_NUMBER(),
         @ERROR_LINE = ERROR_LINE(),
         @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR_SEVERITY,
            @ERROR_STATE,
            @ERROR_NUMBER,
            @ERROR_LINE,
            @ERROR_MESSAGE);
END CATCH



GO
PRINT N'Creating Event.CreateInvitation...';



GO
CREATE PROCEDURE [Event].[CreateInvitation]
@EventID INT, @BodyText NVARCHAR (MAX)=NULL, @InvitationSent SMALLDATETIME=NULL, @Attendees [Event].[AttendeeList] READONLY, @InvitationID INT OUTPUT
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

DECLARE @AccountID AS INT;

BEGIN TRY
  BEGIN TRANSACTION
  
  INSERT INTO Event.[Invitations]
  (
    EventID,
    BodyText,
    InvitationSent
  )
  VALUES
  (
    @EventID,
    @BodyText,
    @InvitationSent
  );
  
  SET @InvitationID = SCOPE_IDENTITY();
  
  SELECT @AccountID = OwnerID
    FROM Event.Events
   WHERE EventID = @EventID;
  
  INSERT INTO Event.Attendees
  (
    EventID,
    AccountID,
    EmailAddress
  )
  SELECT @EventID,
         AccountID,
         EmailAddress
    FROM @Attendees;
  
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  SET @InvitationID = -1;

  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  
  DECLARE @ERROR_SEVERITY AS INT,
          @ERROR_STATE AS INT,
          @ERROR_NUMBER AS INT,
          @ERROR_LINE AS INT,
          @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT @ERROR_SEVERITY = ERROR_SEVERITY(),
         @ERROR_STATE = ERROR_STATE(),
         @ERROR_NUMBER = ERROR_NUMBER(),
         @ERROR_LINE = ERROR_LINE(),
         @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR_SEVERITY,
            @ERROR_STATE,
            @ERROR_NUMBER,
            @ERROR_LINE,
            @ERROR_MESSAGE);
END CATCH



GO
PRINT N'Creating Event.CreateNewEvent...';



GO
CREATE PROCEDURE [Event].[CreateNewEvent]
@OwnerID INT, @LocationID INT=NULL, @AttendeeListVisibilityID SMALLINT=1, @EventTitle NVARCHAR (255), @Description NVARCHAR (1000)=NULL, @Cost MONEY=0, @PaymentAccepted BIT=0, @PaymentRefundable BIT=0, @InvitationOnly BIT=0, @Searchable BIT=1, @SearchEngineURL NVARCHAR (200)=NULL, @EventID INT OUTPUT
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

BEGIN TRY
  BEGIN TRANSACTION;
  
  INSERT INTO Event.Events
  (
    OwnerID,
    LocationID,
    AttendeeListVisibilityID,
    Title,
    Description,
    Cost,
    PaymentAccepted,
    PaymentRefundable,
    InvitationOnly,
    Searchable,
    SearchEngineURL
  )
  VALUES
  (
    @OwnerID,
    @LocationId,
    @AttendeeListVisibilityID,
    @EventTitle,
    @Description,
    @Cost,
    @PaymentAccepted,
    @PaymentRefundable,
    @InvitationOnly,
    @Searchable,
    @SearchEngineURL
  )
  
  SET @EventID = SCOPE_IDENTITY();
  
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  SET @EventID = -1;
  
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  
  DECLARE @ERROR_SEVERITY AS INT,
          @ERROR_STATE AS INT,
          @ERROR_NUMBER AS INT,
          @ERROR_LINE AS INT,
          @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT @ERROR_SEVERITY = ERROR_SEVERITY(),
         @ERROR_STATE = ERROR_STATE(),
         @ERROR_NUMBER = ERROR_NUMBER(),
         @ERROR_LINE = ERROR_LINE(),
         @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR_SEVERITY,
            @ERROR_STATE,
            @ERROR_NUMBER,
            @ERROR_LINE,
            @ERROR_MESSAGE);
END CATCH



GO
PRINT N'Creating Event.CreateNewLocation...';



GO
CREATE PROCEDURE [Event].[CreateNewLocation]
@Title NVARCHAR (255), @Address1 NVARCHAR (255)=NULL, @Address2 NVARCHAR (255)=NULL, @City NVARCHAR (50)=NULL, @State NVARCHAR (50)=NULL, @PostalCode NVARCHAR (10)=NULL, @Directions NVARCHAR (2000)=NULL, @LocationID INT OUTPUT, @Coordinates [sys].[geography]=NULL
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;
BEGIN TRY
  BEGIN TRANSACTION;
  
  INSERT INTO [Event].[Locations]
  (
    Address1,
    Address2,
    City,
    State,
    PostalCode,
    Directions,
    Coordinates
  )
  VALUES
  (
    @Address1,
    @Address2,
    @City,
    @State,
    @PostalCode,
    @Directions,
    @Coordinates
  );
  
  SET @LocationID = SCOPE_IDENTITY();
  
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  SET @LocationID = -1;
  
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  
  DECLARE @ERROR_SEVERITY AS INT,
          @ERROR_STATE AS INT,
          @ERROR_NUMBER AS INT,
          @ERROR_LINE AS INT,
          @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT @ERROR_SEVERITY = ERROR_SEVERITY(),
         @ERROR_STATE = ERROR_STATE(),
         @ERROR_NUMBER = ERROR_NUMBER(),
         @ERROR_LINE = ERROR_LINE(),
         @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR_SEVERITY,
            @ERROR_STATE,
            @ERROR_NUMBER,
            @ERROR_LINE,
            @ERROR_MESSAGE);
END CATCH



GO
PRINT N'Creating Event.DeleteEvent...';



GO
CREATE PROCEDURE [Event].[DeleteEvent]
@EventID INT, @OwnerID INT
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

BEGIN TRY
  BEGIN TRANSACTION
  
  DELETE es
    FROM Event.Schedule AS es
         INNER JOIN Event.Events AS ee
            ON es.EventID = ee.EventID
   WHERE ee.EventID = @EventID
     AND ee.OwnerID = @OwnerID;
  
  DELETE Event.Events
   WHERE EventID = @EventID
     AND OwnerID = @OwnerID;
  
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  
  DECLARE @ERROR_SEVERITY AS INT,
          @ERROR_STATE AS INT,
          @ERROR_NUMBER AS INT,
          @ERROR_LINE AS INT,
          @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT @ERROR_SEVERITY = ERROR_SEVERITY(),
         @ERROR_STATE = ERROR_STATE(),
         @ERROR_NUMBER = ERROR_NUMBER(),
         @ERROR_LINE = ERROR_LINE(),
         @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR_SEVERITY,
            @ERROR_STATE,
            @ERROR_NUMBER,
            @ERROR_LINE,
            @ERROR_MESSAGE);
END CATCH



GO
PRINT N'Creating Event.GetEvent...';



GO
CREATE PROCEDURE [Event].[GetEvent]
@EventID INT
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

SELECT ee.EventID,
       ee.OwnerID,
       aa.DisplayName AS OwnerDisplayName,
       ee.AttendeeListVisibilityID,
       ealv.AttendeeListVisibilityDescription,
       ee.Title,
       ee.Description,
       ee.Cost,
       ee.PaymentAccepted,
       ee.PaymentRefundable,
       ee.InvitationOnly,
       ee.Searchable,
       ee.SearchEngineURL,
       el.LocationID,
       el.Title,
       el.Address1,
       el.Address2,
       el.City,
       el.State,
       el.PostalCode,
       el.Directions
  FROM Event.Events AS ee
       INNER JOIN Event.AttendeeListVisibility AS ealv
          ON ee.AttendeeListVisibilityID = ealv.AttendeeListVisibilityID
       INNER JOIN Auth.Accounts AS aa
          ON ee.OwnerID = aa.AccountID
        LEFT OUTER JOIN Event.Locations AS el
          ON ee.LocationID = el.LocationID
 WHERE EventID = @EventID;

SELECT es.ScheduleID,
       es.Description,
       es.StartTime,
       es.EndTime
  FROM Event.Schedule AS es
 WHERE es.EventID = @EventID;



GO
PRINT N'Creating Event.RegisterAttendance...';



GO
CREATE PROCEDURE [Event].[RegisterAttendance]
@EventID INT, @AccountID INT
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

DECLARE @AttendeeCount AS INT = 0;

BEGIN TRY
  BEGIN TRANSACTION
  
  SELECT @AttendeeCount = COUNT(*)
    FROM Event.Attendees
   WHERE EventID = @EventID
     AND AccountID = @AccountID;
  
  IF @AttendeeCount > 0
    RETURN
  
  INSERT INTO Event.Attendees
  (
    EventID,
    AccountID
  )
  VALUES 
  (
    @EventID, 
    @AccountID
  );
  
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  
  DECLARE @ERROR_SEVERITY AS INT,
          @ERROR_STATE AS INT,
          @ERROR_NUMBER AS INT,
          @ERROR_LINE AS INT,
          @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT @ERROR_SEVERITY = ERROR_SEVERITY(),
         @ERROR_STATE = ERROR_STATE(),
         @ERROR_NUMBER = ERROR_NUMBER(),
         @ERROR_LINE = ERROR_LINE(),
         @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR_SEVERITY,
            @ERROR_STATE,
            @ERROR_NUMBER,
            @ERROR_LINE,
            @ERROR_MESSAGE);
END CATCH



GO
PRINT N'Creating Event.RSVP...';



GO
CREATE PROCEDURE [Event].[RSVP]
@EventID INT, @AccountID INT=NULL, @EmailAddress NVARCHAR (255)=NULL, @Response BIT=NULL
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

IF @AccountID IS NULL AND @EmailAddress IS NULL
BEGIN
  RAISERROR('Either AccountID or EmailAddress must be supplied', 15, 0);
  RETURN;
END

DECLARE @sql AS NVARCHAR(MAX);
DECLARE @newline AS NVARCHAR(2);

SET @newline = NCHAR(13) + NCHAR(10);
SET @sql  = ' /* Event.RSVP */ 
UPDATE Event.Attendees
   SET Response = @response
 WHERE 1 = 1 ' + @newline;

IF @EmailAddress IS NOT NULL
  SET @sql += '   AND EmailAddress = @email_address ' + @newline;

IF @AccountID IS NOT NULL
  SET @sql += '   AND AccountID = @account_id ' + @newline;

BEGIN TRY
  BEGIN TRANSACTION
  
  EXEC sp_executesql @sql,
       N'@email_address NVARCHAR(255), @account_id INT, @response BIT',
       @EmailAddress, @AccountID, @Response;
  
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  
  DECLARE @ERROR_SEVERITY AS INT,
          @ERROR_STATE AS INT,
          @ERROR_NUMBER AS INT,
          @ERROR_LINE AS INT,
          @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT @ERROR_SEVERITY = ERROR_SEVERITY(),
         @ERROR_STATE = ERROR_STATE(),
         @ERROR_NUMBER = ERROR_NUMBER(),
         @ERROR_LINE = ERROR_LINE(),
         @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR_SEVERITY,
            @ERROR_STATE,
            @ERROR_NUMBER,
            @ERROR_LINE,
            @ERROR_MESSAGE);
END CATCH



GO
PRINT N'Creating Event.UpdateEvent...';



GO
CREATE PROCEDURE [Event].[UpdateEvent]
@EventID INT, @LocationID INT=NULL, @AttendeeListVisibilityID SMALLINT=1, @EventTitle NVARCHAR (255), @Description NVARCHAR (1000)=NULL, @Cost MONEY=0, @PaymentAccepted BIT=0, @PaymentRefundable BIT=0, @InvitationOnly BIT=0, @Searchable BIT=1, @SearchEngineURL NVARCHAR (200)=NULL
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

BEGIN TRY
  BEGIN TRANSACTION
  
  UPDATE Event.Events
     SET LocationId = @LocationID,
         AttendeeListVisibilityID = @AttendeeListVisibilityID,
         Title = @EventTitle,
         Description = @Description,
         Cost = @Cost,
         PaymentAccepted = @PaymentAccepted,
         PaymentRefundable = @PaymentRefundable,
         InvitationOnly = @InvitationOnly,
         Searchable = @Searchable,
         SearchEngineURL = @SearchEngineURL
   WHERE EventID = @EventId;
  
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  DECLARE @ERROR_SEVERITY AS INT,
          @ERROR_STATE AS INT,
          @ERROR_NUMBER AS INT,
          @ERROR_LINE AS INT,
          @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT @ERROR_SEVERITY = ERROR_SEVERITY(),
         @ERROR_STATE = ERROR_STATE(),
         @ERROR_NUMBER = ERROR_NUMBER(),
         @ERROR_LINE = ERROR_LINE(),
         @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR_SEVERITY,
            @ERROR_STATE,
            @ERROR_NUMBER,
            @ERROR_LINE,
            @ERROR_MESSAGE);
END CATCH



GO
PRINT N'Creating Event.UpdateInvitation...';



GO
CREATE PROCEDURE [Event].[UpdateInvitation]
@InvitationID INT, @EventID INT, @BodyText NVARCHAR (MAX)=NULL, @InvitationSent SMALLDATETIME=NULL
WITH EXECUTE AS OWNER
AS
BEGIN TRY
  BEGIN TRANSACTION
  
  UPDATE Event.[Invitations]
     SET EventID = @EventID,
         BodyText = @BodyText,
         InvitationSent = @InvitationSent
   WHERE InvitationID = @InvitationID;
  
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  
  DECLARE @ERROR_SEVERITY AS INT,
          @ERROR_STATE AS INT,
          @ERROR_NUMBER AS INT,
          @ERROR_LINE AS INT,
          @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT @ERROR_SEVERITY = ERROR_SEVERITY(),
         @ERROR_STATE = ERROR_STATE(),
         @ERROR_NUMBER = ERROR_NUMBER(),
         @ERROR_LINE = ERROR_LINE(),
         @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR_SEVERITY,
            @ERROR_STATE,
            @ERROR_NUMBER,
            @ERROR_LINE,
            @ERROR_MESSAGE);
END CATCH



GO
PRINT N'Creating Event.UpdateLocation...';



GO
CREATE PROCEDURE [Event].[UpdateLocation]
@LocationID INT, @Title NVARCHAR (255), @Address1 NVARCHAR (255)=NULL, @Address2 NVARCHAR (255)=NULL, @City NVARCHAR (50)=NULL, @State NVARCHAR (50)=NULL, @PostalCode NVARCHAR (10)=NULL, @Directions NVARCHAR (2000)=NULL, @Coordinates [sys].[geography]=NULL
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;
BEGIN TRY
  BEGIN TRANSACTION
  
  UPDATE Event.Locations
     SET Title = @Title,
         Address1 = @Address1,
         Address2 = @Address2,
         City = @City,
         State = @State,
         PostalCode = @PostalCode,
         Directions = @Directions,
         Coordinates = @Coordinates
   WHERE LocationID = @LocationID;
  
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  
  DECLARE @ERROR_SEVERITY AS INT,
          @ERROR_STATE AS INT,
          @ERROR_NUMBER AS INT,
          @ERROR_LINE AS INT,
          @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT @ERROR_SEVERITY = ERROR_SEVERITY(),
         @ERROR_STATE = ERROR_STATE(),
         @ERROR_NUMBER = ERROR_NUMBER(),
         @ERROR_LINE = ERROR_LINE(),
         @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR_SEVERITY,
            @ERROR_STATE,
            @ERROR_NUMBER,
            @ERROR_LINE,
            @ERROR_MESSAGE);
END CATCH



GO
PRINT N'Creating Event.UpdateSchedule...';



GO
CREATE PROCEDURE [Event].[UpdateSchedule]
@ScheduleID INT, @Description NVARCHAR (255)=NULL, @StartTime SMALLDATETIME, @EndTime SMALLDATETIME
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

BEGIN TRY
  BEGIN TRANSACTION
  
  UPDATE Event.Schedule
     SET Description = @Description,
         StartTime = @StartTime,
         EndTime = @EndTime
   WHERE ScheduleID = @ScheduleID;
  
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  
  DECLARE @ERROR_SEVERITY AS INT,
          @ERROR_STATE AS INT,
          @ERROR_NUMBER AS INT,
          @ERROR_LINE AS INT,
          @ERROR_MESSAGE AS NVARCHAR(4000);

  SELECT @ERROR_SEVERITY = ERROR_SEVERITY(),
         @ERROR_STATE = ERROR_STATE(),
         @ERROR_NUMBER = ERROR_NUMBER(),
         @ERROR_LINE = ERROR_LINE(),
         @ERROR_MESSAGE = ERROR_MESSAGE();

  RAISERROR('Msg %d, Line %d, :%s',
            @ERROR_SEVERITY,
            @ERROR_STATE,
            @ERROR_NUMBER,
            @ERROR_LINE,
            @ERROR_MESSAGE);
END CATCH



GO


GO
/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/
SET IDENTITY_INSERT Auth.AuthenticationType ON;

GO

IF (SELECT COUNT(*) FROM Auth.AuthenticationType WHERE AuthenticationTypeName = 'EventHandler') = 0
  INSERT INTO Auth.AuthenticationType (AuthenticationTypeID, AuthenticationTypeName)
  VALUES (1, 'EventHandler');

IF (SELECT COUNT(*) FROM Auth.AuthenticationType WHERE AuthenticationTypeName = 'OpenID') = 0
  INSERT INTO Auth.AuthenticationType (AuthenticationTypeID, AuthenticationTypeName)
  VALUES (2, 'OpenID');

IF (SELECT COUNT(*) FROM Auth.AuthenticationType WHERE AuthenticationTypeName = 'LiveID') = 0
  INSERT INTO Auth.AuthenticationType (AuthenticationTypeID, AuthenticationTypeName)
  VALUES (3, 'LiveID');

GO

SET IDENTITY_INSERT Auth.AuthenticationType OFF;

GO

SET IDENTITY_INSERT Event.AttendeeListVisibility ON;

GO

IF (SELECT COUNT(*) FROM Event.AttendeeListVisibility WHERE AttendeeListVisibilityDescription = 'Public') = 0
  INSERT INTO Event.AttendeeListVisibility (AttendeeListVisibilityID, AttendeeListVisibilityDescription) VALUES (1, 'Public');

IF (SELECT COUNT(*) FROM Event.AttendeeListVisibility WHERE AttendeeListVisibilityDescription = 'Public') = 0
  INSERT INTO Event.AttendeeListVisibility (AttendeeListVisibilityID, AttendeeListVisibilityDescription) VALUES (2, 'Viewable to attendees');

IF (SELECT COUNT(*) FROM Event.AttendeeListVisibility WHERE AttendeeListVisibilityDescription = 'Public') = 0
  INSERT INTO Event.AttendeeListVisibility (AttendeeListVisibilityID, AttendeeListVisibilityDescription) VALUES (3, 'Viewable to paid attendees');

IF (SELECT COUNT(*) FROM Event.AttendeeListVisibility WHERE AttendeeListVisibilityDescription = 'Public') = 0
  INSERT INTO Event.AttendeeListVisibility (AttendeeListVisibilityID, AttendeeListVisibilityDescription) VALUES (4, 'Private');

GO

SET IDENTITY_INSERT Event.AttendeeListVisibility OFF;

GO

-- Grant permissions to stored procedures
GRANT EXECUTE ON Auth.CreateNewAccount TO EventHandlerWeb;
GRANT EXECUTE ON Auth.DeleteAccount TO EventHandlerWeb;
GRANT EXECUTE ON Auth.GetUserByAccountID TO EventHandlerWeb;
GRANT EXECUTE ON Auth.GetUserByUserName TO EventHandlerWeb;
GRANT EXECUTE ON Auth.UpdateAccount TO EventHandlerWeb;
GRANT EXECUTE ON Event.AddSchedule TO EventHandlerWeb;
GRANT EXECUTE ON Event.CancelAttendance TO EventHandlerWeb;
GRANT EXECUTE ON Event.CreateInvitation TO EventHandlerWeb;
GRANT EXECUTE ON Event.CreateNewEvent TO EventHandlerWeb;
GRANT EXECUTE ON Event.CreateNewLocation TO EventHandlerWeb;
GRANT EXECUTE ON Event.DeleteEvent TO EventHandlerWeb;
GRANT EXECUTE ON Event.GetEvent TO EventHandlerWeb;
GRANT EXECUTE ON Event.RSVP TO EventHandlerWeb;
GRANT EXECUTE ON Event.UpdateEvent TO EventHandlerWeb;
GRANT EXECUTE ON Event.UpdateInvitation TO EventHandlerWeb;
GRANT EXECUTE ON Event.UpdateLocation TO EventHandlerWeb;
GRANT EXECUTE ON Event.UpdateSchedule TO EventHandlerWeb;

GO


GO


GO


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'EventHandlerDatabase_Release')
    BEGIN
        DECLARE @VarDecimalSupported AS BIT;
        SELECT @VarDecimalSupported = 0;
        IF ((ServerProperty(N'EngineEdition') = 3)
            AND (((@@microsoftversion / power(2, 24) = 9)
                  AND (@@microsoftversion & 0xffff >= 3024))
                 OR ((@@microsoftversion / power(2, 24) = 10)
                     AND (@@microsoftversion & 0xffff >= 1600))))
            SELECT @VarDecimalSupported = 1;
        IF (@VarDecimalSupported > 0)
            BEGIN
                EXECUTE sp_db_vardecimal_storage_format N'EventHandlerDatabase_Release', 'ON';
            END
    END



GO
